# -*- coding: utf-8 -*-
"""
Database operations/MySql Aiomysql/test03.py
"""
import asyncio

from sqlalchemy import Column, Integer, String, Table
from sqlalchemy import create_engine
from sqlalchemy import MetaData

from aiomysql.sa import create_engine
from aiomysql import Cursor

tb_user = Table(
    'user',
    MetaData(),
    Column('user_id', type_=Integer, primary_key=True),
    Column('user_name', type_=String(500)),
)


async def connection():
    """
    建立数据库会话池
    :return:
    """
    engine = await create_engine(
        maxsize=10, minsize=1, host="127.0.0.1", user='tornado',
        password="tornado",
        db='tornado_db_02', port=3306, unix_socket=None,
        charset='', sql_mode=None,
        read_default_file=None, use_unicode=None,
        client_flag=0, cursorclass=Cursor, init_command=None,
        connect_timeout=None, read_default_group=None,
        autocommit=False, echo=False,
        local_infile=False, loop=None, ssl=None, auth_plugin='',
        program_name='', server_public_key=None
    )
    return engine


def print_engine(engine):
    """
    打印会话属性
    :param engine:
    :return:
    """
    # dialect: sqlalchemy.engine.interfaces.Dialect引擎，只读属性。
    print(f'aiomysql.sa.Engine.dialect:               {engine.dialect}')
    # name: dialect的名称，只读属性。
    print(f'aiomysql.sa.Engine.name:                  {engine.name}')
    # driver: dialect的驱动程序，只读属性。
    print(f'aiomysql.sa.Engine.driver:                {engine.driver}')
    # minsize: 连接池最小连接数。缺省值：`1`，只读属性。
    print(f'aiomysql.sa.Engine.minsize:               {engine.minsize}')
    # maxsize: 连接池最大连接数。缺省值: `10`，只读属性。
    print(f'aiomysql.sa.Engine.maxsize:               {engine.maxsize}')
    # size: 当前连接池中连接数，包含已使用和空闲连接数，只读属性。
    print(f'aiomysql.sa.Engine.size:                  {engine.size}')
    # freesize: 连接池中空闲连接数，只读属性。
    print(f'aiomysql.sa.Engine.freesize:              {engine.freesize}')


def print_connect(con):
    """
    打印数据库会话信息
    :return:
    """
    # SAConnection.connection.get_autocommit(): 获取当前数据库会话的自动提交状态
    print(f'SAConnection.connection.get_autocommit(): {con.connection.get_autocommit()}')

    # SAConnection.connection.host: 获取数据库会话地址
    print(f'SAConnection.connection.host:             {con.connection.host}')

    # SAConnection.connection.port: 获取数据库会话端口
    print(f'SAConnection.connection.port:             {con.connection.port}')

    # SAConnection.connection.unix_socket: 获取数据库会话端口
    print(f'SAConnection.connection.unix_socket:      {con.connection.unix_socket}')

    # SAConnection.connection.db: 获取当前数据库名
    print(f'SAConnection.connection.db:               {con.connection.db}')

    # SAConnection.connection.user: 获取数据库会话用户名
    print(f'SAConnection.connection.user:             {con.connection.user}')

    # SAConnection.connection: 返回echo模式状态。
    print(f'SAConnection.connection.echo:             {con.connection.echo}')

    # SAConnection.connection.encoding:  获取数据库会话编码
    print(f'SAConnection.connection.encoding:         {con.connection.encoding}')

    # SAConnection.connection.charset： 获取当前数据库会话字符集
    print(f'SAConnection.connection.charset:          {con.connection.charset}')

    # SAConnection.closed：  如果连接关闭，则返回True。只读属性。
    print(f'SAConnection.closed:                      {con.closed}')

    # SAConnection.in_transaction:  如果事务正在进行，则返回 True 的只读属性。
    print(f'SAConnection.in_transaction:              {con.in_transaction}')


async def process_users(conn):
    """
    处理用户数据
    :return:
    """
    # begin():  开始一个事务并返回一个事务句柄。
    async with conn.begin() as transaction:
        # execute(`query, *multiparams, **params`):  使用可选参数执行 SQL 查询。
        await conn.execute(tb_user.insert().values(user_name='tornado'))
        await transaction.commit()

        res = await conn.execute(tb_user.select())
        async for row in res:
            print(row.user_id, row.user_name)


async def main():
    engine = await connection()

    # 打印会话属性
    print_engine(engine)

    # acquire(): 从空闲池中获取连接。
    conn = await engine.acquire()
    # 打印数据库会话信息
    print_connect(conn)
    # 处理用户数据
    # await process_users(conn)
    # close():  关闭此 SAConnection。
    await conn.close()

    # close(): 关闭引擎。
    engine.close()
    # wait_closed(): 等待释放和关闭所有获取的连接的协程。
    await engine.wait_closed()


if __name__ == '__main__':
    asyncio.run(main())
